﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.IO;

using System.Data;
using System.Data.SQLite;

namespace Stone
{
    public class DB
    {
        private SQLiteConnection connessioneAlDB = null;
        private string connectionString = "n/a";
        private SQLiteTransaction transaction = null;

        public DB(string dbFilePath)
        {
            string queryNewDb = "CREATE TABLE nodes ( id INTEGER NOT NULL PRIMARY KEY, lon REAL NOT NULL, lat REAL NOT NULL ); CREATE TABLE node_tags (	ref INTEGER NOT NULL, key TEXT,	value TEXT ); CREATE TABLE way_nodes ( way INTEGER NOT NULL, num INTEGER NOT NULL, node INTEGER NOT NULL ); CREATE TABLE way_tags (	ref INTEGER NOT NULL, key TEXT, value TEXT ); CREATE TABLE ways ( id INTEGER NOT NULL PRIMARY KEY ); CREATE TABLE ways_full ( id_way INTEGER NOT NULL , id_node INTEGER NOT NULL , num INTEGER , name TEXT, node_lat REAL, node_lon REAL, type TEXT , subtype TEXT ); create index indexNodeIds on ways_full (id_node); create index indexWayIds on ways_full (id_way); create index indexNodeNums on ways_full (num); create index indexNodeLat on ways_full (node_lat); create index indexNodeLon on ways_full (node_lon);";
            
            if(!File.Exists(dbFilePath))
                CreateDB(dbFilePath, queryNewDb);
            else
                connectionString = "data source=\"" + dbFilePath + "\"";
        }

        public void ExecuteNonQuery(string query)
        {
            SQLiteCommand q = new SQLiteCommand(query, connessioneAlDB);
            q.ExecuteNonQuery();
        }

        public DataTable ExecuteQuery(string query)
        {
            DataTable result = null;

            SQLiteCommand q = new SQLiteCommand(query, connessioneAlDB);
            SQLiteDataAdapter da = new SQLiteDataAdapter(q);
            DataSet ds = new DataSet();
            ds.Reset();
            da.Fill(ds);
            result = ds.Tables[0];

            return result;
        }

        public void Connect()
        {
            connessioneAlDB = new SQLiteConnection(connectionString);
            connessioneAlDB.Open();
        }
        public void BeginTransaction()
        {
            transaction = connessioneAlDB.BeginTransaction();
        }

        public void EndTransaction()
        {
            transaction.Commit();
        }

        private void CreateDB(string path, string sql)
        {
            SQLiteConnection.CreateFile(path);
            connectionString = "data source=\"" + path + "\"";
            Connect();
            ExecuteNonQuery(sql);
        }

        public SQLiteDataReader ExecuteQueryAndPassMeTheReader(string query)
        {
            SQLiteDataReader result = null;

            SQLiteCommand q = new SQLiteCommand(query, connessioneAlDB);
            result = q.ExecuteReader();

            return result;
        }
    }
}


